package com.zaofan.utils;

import com.zaofan.annotation.AutoIncrement;
import com.zaofan.annotation.DateFormat;
import com.zaofan.exception.NotExsitAutoIncrementFiled;
import com.zaofan.exception.NotExsitId;
import com.zaofan.exception.QueryLimitError;
import com.zaofan.exception.UpdateColumnIsEmpty;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.regex.Pattern;

public class SqlBuilderUtils {

	public static <T> String builderInsertSql(Class<T> clazz, T bean) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		String column = "";
		String values = "";
		sb.append("insert into " + tableName + "(");
		try {
			Field[] fields = bean.getClass().getDeclaredFields();
			for (Field field : fields) {
				boolean isAutoIncrement = field.isAnnotationPresent(AutoIncrement.class);
				if (isAutoIncrement) {
					continue;
				}
				String key = field.getName();
				field.setAccessible(true);
				Object v = field.get(bean);
				if (v == null) {
					continue;
				}
				column += key + ",";
				if (v instanceof Long) {
					values += v + ",";
				} else if (v instanceof Integer) {
					values += v + ",";
				} else if (v instanceof Date) {
					DateFormat dateFormat = field.getAnnotation(DateFormat.class);
					if (dateFormat != null) {
						String pattern = dateFormat.format();
						SimpleDateFormat df = new SimpleDateFormat(pattern);
						String date = df.format(v);
						values += "'" + date + "'" + ",";
					} else {
						SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						values += "'" + df.format(v) + "'" + ",";
					}
				} else {
					values += "'" + v + "'" + ",";
				}
			}
			column = column.substring(0, column.length() - 1);
			values = values.substring(0, values.length() - 1);
			sb.append(column + ")");
			sb.append(" values(" + values + ")");
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static <T> String builderDelSql(Class<T> clazz, T bean) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		sb.append("delete from " + tableName + " where ");
		try {
			Field[] fields = bean.getClass().getDeclaredFields();
			int i = 0;
			for (i = 0; i < fields.length; i++) {
				boolean isAutoIncrement = fields[i].isAnnotationPresent(AutoIncrement.class);
				if (isAutoIncrement) {
					sb.append(fields[i].getName() + " = ");
					fields[i].setAccessible(true);
					Object v = fields[i].get(bean);
					if (v == null) {
						throw new NotExsitId("删除对象自动增长列不能为�??");
					} else {
						sb.append(v.toString());
					}
					break;
				}
			}
			if (i >= fields.length) {
				throw new NotExsitAutoIncrementFiled("删除对象不存在自动增长列");
			}
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static <T> String builderUpdateSql(Class<T> clazz, T bean) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		String setValue = "";
		String where = "";
		sb.append("update " + tableName);
		try {
			Field[] fields = bean.getClass().getDeclaredFields();
			for (Field field : fields) {
				boolean isAutoIncrement = field.isAnnotationPresent(AutoIncrement.class);
				if (isAutoIncrement) {
					field.setAccessible(true);
					Object v = field.get(bean);
					if (v == null) {
						throw new NotExsitId("id不能为空");
					}
					where = " where " + field.getName() + " = " + field.get(bean);
					continue;
				}
				String key = field.getName();
				field.setAccessible(true);
				Object v = field.get(bean);
				if (v == null) {
					continue;
				}
				
				if (v instanceof Long) {
					setValue += key + " = ";
					setValue += v + ",";
				} else if (v instanceof Integer) {
					setValue += key + " = ";
					setValue += v + ",";
				} else if (v instanceof Date) {
					setValue += key + " = ";
					DateFormat dateFormat = field.getAnnotation(DateFormat.class);
					if (dateFormat != null) {
						String pattern = dateFormat.format();
						SimpleDateFormat df = new SimpleDateFormat(pattern);
						String date = df.format(v);
						setValue += "'" + date + "'" + ",";
					} else {
						SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						setValue += "'" + df.format(v) + "'" + ",";
					}
				} else if (v instanceof Double) {
					setValue += key + " = ";
					setValue += "'" + v + "'" + ",";
				} else {
					if(WebUtils.isEmpty(v)){
						continue;
					}
					setValue += key + " = ";
					setValue += "'" + v + "'" + ",";
				}
			}
			if (!"".equals(setValue)) {
				setValue = setValue.substring(0, setValue.length() - 1);
			}
			if (setValue.length() > 0) {
				setValue = " set " + setValue;
			} else {
				throw new UpdateColumnIsEmpty("修改字段不能全为�??");
			}
			sb.append(setValue);
			sb.append(where);
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static <T> String builderFindSql(Class<T> clazz, Map<String, Object> params) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		String where = "";
		String limit = "";
		String order = "";
		String begin = "";
		String end = "";
		sb.append("select * from " + tableName);
		try {
			int index = 0;
			if (params != null) {
				for (Map.Entry<String, Object> entry : params.entrySet()) {
					String key = entry.getKey();
					Object value = entry.getValue();
					if (value == null) {
						continue;
					}
					if (key.equals("begin")) {
						begin = (String) value;
						continue;
					}
					if (key.equals("end")) {
						end = (String) value;
						continue;
					}
					if (key.equals("mark")) {
						where += " where mark "  + " like " + "'" + value + "%'" + ",";
						continue;
					}
					if (key.equals("order")) {
						order += value;
						continue;
					}
					
					if (index == 0) {
						where += " where ";
						index++;
					}

					if (key.equals("orderTime")) {
						String v = value .toString();
						if (v.indexOf("between") > -1) {
							where += " date("+key+")"  + " >= " + "'" + v.split("between")[0] + "'" + " and date("+key+") <= " + "'"+v.split("between")[1] + "',";
						}else{
							where += " date("+key+")"  + "=" + "'" + value + "'" + ",";
						}
						continue;
					}
					if (key.equals("regeistDate")) {
						String v = value .toString();
							where += " date("+key+")"  + " >= " + "'" + v.split("between")[0] + "'" + " and date("+key+") <= " + "'"+v.split("between")[1] + "',";

						continue;
					}
					if (key.equals("bookDate")) {
						where += " date("+key+")"  + "=" + "'" + value + "'" + ",";
						continue;
					}
					if (value instanceof Long) {
						where += key + "=" + value + ",";
					} else if (value instanceof Integer) {
						where += key + "=" + value + ",";
					} else if (value instanceof Date) {
						DateFormat dateFormat = clazz.getDeclaredField(key).getAnnotation(DateFormat.class);
						if (dateFormat != null) {
							String pattern = dateFormat.format();
							SimpleDateFormat df = new SimpleDateFormat(pattern);
							String date = df.format(value);
							where += key + "=" + "'" + date + "'" + ",";
						} else {
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							where += key + "=" + "'" + df.format(value) + "'" + ",";
						}
					} else {
						String string = (String) value;
						if(string.startsWith("in")){
							where += key + " in " + "(" + string.replace("in", "").replace(",", "*") + ")" + ",";
						}else{
							where += key + "=" + "'" + value + "'" + ",";
						}
					}
				}
			}
			if (!"".equals(where)) {
				where = where.substring(0, where.length() - 1);
				where = where.replace(",", " and ");
				where = where.replace("*", ",");
				sb.append(where);
			}
			if (!"".equals(order)) {
				sb.append(" order " + order);
			}
			if (!"".equals(begin) && !"".equals(end)) {
				limit = begin + "," + end;

				System.err.println("limit = " + limit);
				String pattern = "[0-9]+,[0-9]+";
				if (Pattern.matches(pattern, limit)) {
					sb.append(" limit " + limit);
				} else {
					throw new QueryLimitError("分页参数错误");
				}

			}
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static <T> String builderCountSql(Class<T> clazz, Map<String, Object> params) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		String where = "";
		sb.append("select count(*) from " + tableName);
		try {
			int index = 0;
			if (params != null) {
				for (Map.Entry<String, Object> entry : params.entrySet()) {
					String key = entry.getKey();
					Object value = entry.getValue();
					if (value == null) {
						continue;
					}
					if (key.equals("begin")) {
						continue;
					}
					if (key.equals("end")) {
						continue;
					}
					if (key.equals("order")) {
						continue;
					}
					if (index == 0) {
						where += " where ";
						index++;
					}
					if (key.equals("orderTime")) {
						String v = value .toString();
						if (v.indexOf("between") > -1) {
							where += " date("+key+")"  + " >= " + "'" + v.split("between")[0] + "'" + " and date("+key+") <= " + "'"+v.split("between")[1] + "',";
						}else{
							where += " date("+key+")"  + "=" + "'" + value + "'" + ",";
						}
						continue;
					}
					if (key.equals("regeistDate")) {
						String v = value .toString();
						where += " date("+key+")"  + " >= " + "'" + v.split("between")[0] + "'" + " and date("+key+") <= " + "'"+v.split("between")[1] + "',";

						continue;
					}
					if (key.equals("bookDate")) {
						where += " date("+key+")"  + "=" + "'" + value + "'" + ",";
						continue;
					}
					if (value instanceof Long) {
						where += key + "=" + value + ",";
					} else if (value instanceof Integer) {
						where += key + "=" + value + ",";
					} else if (value instanceof Date) {
						DateFormat dateFormat = clazz.getDeclaredField(key).getAnnotation(DateFormat.class);
						if (dateFormat != null) {
							String pattern = dateFormat.format();
							SimpleDateFormat df = new SimpleDateFormat(pattern);
							String date = df.format(value);
							where += key + "=" + "'" + date + "'" + ",";
						} else {
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							where += key + "=" + "'" + df.format(value) + "'" + ",";
						}
					} else {
						String string = (String) value;
						if(string.contains("in")){
							where += key + " in " + "(" + string.replace("in", "").replace(",", "*") + ")" + ",";
						}else{
							where += key + "=" + "'" + value + "'" + ",";
						}
					}
				}
			}
			if (!"".equals(where)) {
				where = where.substring(0, where.length() - 1);
				where = where.replace(",", " and ");
				where = where.replace("*", ",");
				sb.append(where);
			}
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	
	public static <T> String builderDeleteSql(Class<T> clazz, Map<String, Object> params) {
		StringBuilder sb = new StringBuilder();
		String tableName = clazz.getSimpleName().toLowerCase();
		String where = "";
		int index = 0;
		sb.append("delete from " + tableName);
		try {
			if (params != null) {
				for (Map.Entry<String, Object> entry : params.entrySet()) {
					String key = entry.getKey();
					Object value = entry.getValue();
					if (value == null) {
						continue;
					}
					if (index == 0) {
						where += " where ";
						index++;
					}
					if (value instanceof Long) {
						where += key + "=" + value + ",";
					} else if (value instanceof Integer) {
						where += key + "=" + value + ",";
					} else if (value instanceof Date) {
						DateFormat dateFormat = clazz.getDeclaredField(key).getAnnotation(DateFormat.class);
						if (dateFormat != null) {
							String pattern = dateFormat.format();
							SimpleDateFormat df = new SimpleDateFormat(pattern);
							String date = df.format(value);
							where += key + "=" + "'" + date + "'" + ",";
						} else {
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							where += key + "=" + "'" + df.format(value) + "'" + ",";
						}
					} else {
						where += key + "=" + "'" + value + "'" + ",";
					}
				}
			}
			if (!"".equals(where)) {
				where = where.substring(0, where.length() - 1);
				where = where.replace(",", " and ");
				sb.append(where);
			}
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

}
